﻿Imports Entidad
Imports System.Data
Imports System.Data.SqlClient
Public Class BarraDatos

    Function obtenerDatosBarra(barra As String) As ArticuloCodigoBarra
        Dim ab As ArticuloCodigoBarra = Nothing
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT CIA,ART_Codigo,PROV_Codigo,BAR_Codigo,UNM_Codigo,APC_Cantidad " &
                              "FROM VW_ConsultaBarra " &
                              "WHERE BAR_Codigo = '" & barra & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If ds.Tables(0).Rows.Count <> 0 Then
            Dim cia As String = ds.Tables(0).Rows(0)("CIA").ToString.Trim
            Dim articulo As String = ds.Tables(0).Rows(0)("ART_Codigo").ToString.Trim
            Dim proveedor As String = ds.Tables(0).Rows(0)("PROV_Codigo").ToString.Trim
            Dim codBarra As String = ds.Tables(0).Rows(0)("BAR_Codigo").ToString.Trim
            Dim medida As String = ds.Tables(0).Rows(0)("UNM_Codigo").ToString.Trim
            Dim cantidad As Integer = CInt(ds.Tables(0).Rows(0)("APC_Cantidad").ToString.Trim)

            ab = New ArticuloCodigoBarra(cia, articulo, proveedor, codBarra, medida, cantidad)
        End If

        Return ab
    End Function

    Function validarBandeja(codigoBandeja As String) As Boolean
        Dim valor As Boolean = False
        Dim ab As ArticuloCodigoBarra = Nothing
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT COUNT(*) AS CONTAR " &
                              "FROM Bandeja " &
                              "WHERE BAN_CIA+BAN_SUC+BAN_ALM+BAN_Codigo = '" & codigoBandeja & "' " &
                              "AND BAN_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If ds.Tables(0).Rows(0)("CONTAR").ToString.Trim <> "0" Then
            valor = True
        End If

        Return valor
    End Function

    Function obtenerCodigosBarra(codArticulo As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim con As SqlConnection = conex.conectar

        Try
            con.Open()

            Dim query As String = "SELECT CodBarra, UndMedidaNivel, Cantidad, Nivel, Espacio, Tamanio, Lugar, UNM_Codigo, APC_Codigo, PROV_Codigo  " &
                                  "FROM VW_AvanceNiveles " &
                                  "WHERE Articulo = '" & codArticulo & "'"

            Dim adapter As New SqlDataAdapter(query, con)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function obtenerCodigoAutogenerado() As String
        Dim codigo As String = ""
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim con As SqlConnection = conex.conectar()

        Try
            con.Open()

            Dim query As String = "SELECT 'CB' + RIGHT('00000' + LTRIM(RTRIM(COUNT(*) + 1)),5) AS Generado " &
                                  "FROM ArticuloProveedorCodBarra"

            Dim adapter As New SqlDataAdapter(query, con)
            adapter.Fill(ds)

            codigo = ds.Tables(0).Rows(0)("Generado").ToString.Trim
        Catch ex As Exception
            codigo = "-1"
        Finally
            con.Close()
        End Try

        Return codigo
    End Function

    Function registrarCodigoBarra(barra As ArticuloProveedorBarra) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection

        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "INSERT INTO ArticuloProveedorCodBarra(CIA,ART_Codigo,PROV_Codigo, " &
                                  "BAR_Codigo,UNM_Codigo,APC_Cantidad,APC_Nivel,APC_Fecha,APC_Espacio,APC_Tamanio,APC_Observacion) " &
                                  "VALUES(@cia,@articulo,@proveedor,@barra,@unidad,@cantidad,@nivel,GETDATE(),@esp,@tam,@obs)"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@cia", barra.CIA)
                .Parameters.AddWithValue("@articulo", barra.ART_Codigo)
                .Parameters.AddWithValue("@proveedor", barra.PROV_Codigo)
                .Parameters.AddWithValue("@barra", barra.BAR_Codigo)
                .Parameters.AddWithValue("@unidad", barra.UND_Medida)
                .Parameters.AddWithValue("@cantidad", barra.CANTIDAD)
                .Parameters.AddWithValue("@nivel", barra.ART_Nivel)
                .Parameters.AddWithValue("@esp", barra.APC_Espacio)
                .Parameters.AddWithValue("@tam", barra.APC_Tamanio)
                .Parameters.AddWithValue("@obs", barra.APC_Observacion)
                .ExecuteNonQuery()
            End With

            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    Function actualizarCodigoBarra(barra As ArticuloProveedorBarra) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection

        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "UPDATE ArticuloProveedorCodBarra " &
                                  "SET UNM_Codigo = @unidad, APC_Cantidad = @cantidad, APC_Nivel = @nivel, " &
                                  "APC_Espacio = @esp, APC_Tamanio = @tam, APC_Observacion = @obs " &
                                  "WHERE CIA = @cia AND ART_Codigo = @articulo AND PROV_Codigo = @proveedor AND BAR_Codigo = @barra AND APC_Codigo = @APC"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@cia", barra.CIA)
                .Parameters.AddWithValue("@articulo", barra.ART_Codigo)
                .Parameters.AddWithValue("@proveedor", barra.PROV_Codigo)
                .Parameters.AddWithValue("@barra", barra.BAR_Codigo)
                .Parameters.AddWithValue("@unidad", barra.UND_Medida)
                .Parameters.AddWithValue("@cantidad", barra.CANTIDAD)
                .Parameters.AddWithValue("@nivel", barra.ART_Nivel)
                .Parameters.AddWithValue("@esp", barra.APC_Espacio)
                .Parameters.AddWithValue("@tam", barra.APC_Tamanio)
                .Parameters.AddWithValue("@obs", barra.APC_Observacion)
                .Parameters.AddWithValue("@APC", barra.APC_Codigo)
                .ExecuteNonQuery()
            End With

            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    Function verificarExistenciaBarra(codCIA As String, codArticulo As String, codProveedor As String, codBarra As String, apcCodigo As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim con As SqlConnection = conex.conectar()

        Try
            con.Open()

            Dim query As String = "SELECT * " &
                                  "FROM ArticuloProveedorCodBarra " &
                                  "WHERE CIA = @CIA AND ART_Codigo = @ART AND PROV_Codigo = @PROV AND BAR_Codigo = @BAR AND APC_Codigo = @APC"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@ART", codArticulo)
                .Parameters.AddWithValue("@PROV", codProveedor)
                .Parameters.AddWithValue("@BAR", codBarra)
                .Parameters.AddWithValue("@APC", apcCodigo)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)

        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

    Function eliminarCodigoBarra(codCIA As String, codArticulo As String, codProveedor As String, codBarra As String, codAPC As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection

        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "DELETE ArticuloProveedorCodBarra " &
                                  "WHERE CIA = @cia AND ART_Codigo = @articulo AND PROV_Codigo = @proveedor AND BAR_Codigo = @barra AND APC_Codigo = @APC"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@cia", codCIA)
                .Parameters.AddWithValue("@articulo", codArticulo)
                .Parameters.AddWithValue("@proveedor", codProveedor)
                .Parameters.AddWithValue("@barra", codBarra)
                .Parameters.AddWithValue("@APC", codAPC)
                .ExecuteNonQuery()
            End With

            valor = True
        Catch e As Exception
            valor = False
        Finally
            coneccion.Close()
        End Try

        Return valor
    End Function

    Function obtenerDatoBarraXNivel(artCodigo As String, nivel As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection

        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT APC_Codigo, CIA, ART_Codigo, PROV_Codigo, BAR_Codigo, UNM_Codigo, APC_Cantidad, APC_Nivel, APC_Fecha, " &
                                  "APC_Espacio, APC_Tamanio, APC_Observacion " &
                                  "FROM  ArticuloProveedorCodBarra " &
                                  "WHERE ART_Codigo = @ART AND APC_Nivel = @NIV " &
                                  "AND SUBSTRING(BAR_Codigo,1,2) = 'CB'"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@ART", artCodigo)
                .Parameters.AddWithValue("@NIV", nivel)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

End Class
